﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Convert.DataAccess;
using Convert.LOTSmodel;
using Convert.CPIAMmodel;
using System.Configuration;
using System.Data.OleDb;
namespace Convert.DBconverter
{
    class PurchHistoryConverter
    {
        public static string LotsConnectionString; //= @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\LOTS\lots.mdb";//@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\LOTS\lots.mdb";//ConfigurationManager.ConnectionStrings["LOTStoCPIAMconvert.Properties.Settings.lotsConnectionString"].ConnectionString;
        public static string CpiamConnectionString; //= ConfigurationManager.ConnectionStrings["LOTStoCPIAMconvert.Properties.Settings.CPIAMPOSMaxRetailConnectionString"].ConnectionString;

        public static void ConvertPurchHistoryConverter(string lotsConnectionString, string cpiamConnectionString)
        {
            LotsConnectionString = lotsConnectionString;
            CpiamConnectionString = cpiamConnectionString;
            DataTable DeptGroupsDT = DataTables.CreateDeptGroupsDT();
            OleDbConnection dbConn = new OleDbConnection(LotsConnectionString);
                dbConn.Open();
                using (OleDbCommand command = dbConn.CreateCommand())
                {
                    command.CommandText = string.Format(@"SELECT Format(Orders.OrderTime, 'MM, yyyy') AS MonthYear, OrderInvoiceItems.StockID, SUM(OrderInvoiceItems.QtyInv) AS Qty, SUM(OrderInvoiceItems.RealCost) AS PurValue
                    FROM (Orders INNER JOIN OrderInvoiceItems ON Orders.OrderID = OrderInvoiceItems.OrderID) 
                    WHERE(Orders.Delivered = - 1) AND (OrderInvoiceItems.Delivered = - 1)
                    GROUP BY Format(Orders.OrderTime, 'MM, yyyy'), OrderInvoiceItems.StockID
                    HAVING (OrderInvoiceItems.StockID > 0)");
                    OleDbDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        try
                        {
                            Purchases purchase = new Purchases();
                            int i = 0;
                            purchase.MonthYear = reader.GetString(i);
                            purchase.StockID = reader.GetInt32(++i);
                            purchase.Qty = reader.GetDouble(++i);
                            purchase.PurValue = reader.IsDBNull(++i) ? 0 : reader.GetDouble(i);
                            Update(purchase);
                        }
                        catch (Exception ex)
                        {
                            Logger.LogWarningAndError("Error in Convert PurchHistory ", ex);
                        }
                    }
                }
                dbConn.Close();
        }

        private static void Update(Purchases purchase)
        {
            DateTime strCurMnthTag = DateTime.Now;
            int intCurYear = 0;
            int intHistYear = 0;
            int intCurMnth = 0;
            int intHistMnth = 0;
            float sigPackSize = 1;
            double decPurValue = 0;
            decimal decSalesValue = 0;

            intCurYear = int.Parse(strCurMnthTag.Year.ToString());
            intHistYear = int.Parse(purchase.MonthYear.Substring(purchase.MonthYear.IndexOf(",") + 1).Trim());
            intCurMnth = int.Parse(strCurMnthTag.Month.ToString());
            intHistMnth = int.Parse(purchase.MonthYear.Substring(0, purchase.MonthYear.IndexOf(",")).Trim());

            if (purchase.Qty > 0)
            {
                decPurValue = (purchase.PurValue * purchase.Qty) / 100;

                if ((intCurYear == intHistYear) && (intCurMnth == intHistMnth))
                {
                    UpdateStockCurrentMnthPurchDetails(purchase.Qty, decPurValue, purchase.StockID);
                }
                else
                {
                    if (intHistYear == intCurYear)
                    {
                        if (intHistMnth == 1)
                        {
                            UpdateStockCurrYearPurchMnth1(purchase.Qty, decPurValue, purchase.StockID);
                        }
                        else if (intHistMnth == 2)
                        {
                            UpdateStockCurrYearPurchMnth2(purchase.Qty, decPurValue, purchase.StockID);
                        }
                        else if (intHistMnth == 3)
                        {
                            UpdateStockCurrYearPurchMnth3(purchase.Qty, decPurValue, purchase.StockID);
                        }
                        else if (intHistMnth == 4)
                        {
                            UpdateStockCurrYearPurchMnth4(purchase.Qty, decPurValue, purchase.StockID);
                        }
                        else if (intHistMnth == 5)
                        {
                            UpdateStockCurrYearPurchMnth5(purchase.Qty, decPurValue, purchase.StockID);
                        }
                        else if (intHistMnth == 6)
                        {
                            UpdateStockCurrYearPurchMnth6(purchase.Qty, decPurValue, purchase.StockID);
                        }
                        else if (intHistMnth == 7)
                        {
                            UpdateStockCurrYearPurchMnth7(purchase.Qty, decPurValue, purchase.StockID);
                        }
                        else if (intHistMnth == 8)
                        {
                            UpdateStockCurrYearPurchMnth8(purchase.Qty, decPurValue, purchase.StockID);
                        }
                        else if (intHistMnth == 9)
                        {
                            UpdateStockCurrYearPurchMnth9(purchase.Qty, decPurValue, purchase.StockID);
                        }
                        else if (intHistMnth == 10)
                        {
                            UpdateStockCurrYearPurchMnth10(purchase.Qty, decPurValue, purchase.StockID);
                        }
                        else if (intHistMnth == 11)
                        {
                            UpdateStockCurrYearPurchMnth11(purchase.Qty, decPurValue, purchase.StockID);
                        }
                        else if (intHistMnth == 12)
                        {
                            UpdateStockCurrYearPurchMnth12(purchase.Qty, decPurValue, purchase.StockID);
                        }
                    }
                    else
                    {
                        if (intCurYear - intHistYear == 1)
                        {
                            if (intHistMnth > intCurMnth)
                            {
                                if (intHistMnth == 1)
                                {
                                    UpdateStockCurrYearPurchMnth1(purchase.Qty, decPurValue, purchase.StockID);
                                }
                                else if (intHistMnth == 2)
                                {
                                    UpdateStockCurrYearPurchMnth2(purchase.Qty, decPurValue, purchase.StockID);
                                }
                                else if (intHistMnth == 3)
                                {
                                    UpdateStockCurrYearPurchMnth3(purchase.Qty, decPurValue, purchase.StockID);
                                }
                                else if (intHistMnth == 4)
                                {
                                    UpdateStockCurrYearPurchMnth4(purchase.Qty, decPurValue, purchase.StockID);
                                }
                                else if (intHistMnth == 5)
                                {
                                    UpdateStockCurrYearPurchMnth5(purchase.Qty, decPurValue, purchase.StockID);
                                }
                                else if (intHistMnth == 6)
                                {
                                    UpdateStockCurrYearPurchMnth6(purchase.Qty, decPurValue, purchase.StockID);
                                }
                                else if (intHistMnth == 7)
                                {
                                    UpdateStockCurrYearPurchMnth7(purchase.Qty, decPurValue, purchase.StockID);
                                }
                                else if (intHistMnth == 8)
                                {
                                    UpdateStockCurrYearPurchMnth8(purchase.Qty, decPurValue, purchase.StockID);
                                }
                                else if (intHistMnth == 9)
                                {
                                    UpdateStockCurrYearPurchMnth9(purchase.Qty, decPurValue, purchase.StockID);
                                }
                                else if (intHistMnth == 10)
                                {
                                    UpdateStockCurrYearPurchMnth10(purchase.Qty, decPurValue, purchase.StockID);
                                }
                                else if (intHistMnth == 11)
                                {
                                    UpdateStockCurrYearPurchMnth11(purchase.Qty, decPurValue, purchase.StockID);
                                }
                                else if (intHistMnth == 12)
                                {
                                    UpdateStockCurrYearPurchMnth12(purchase.Qty, decPurValue, purchase.StockID);
                                }
                            }
                            else
                            {
                                if (intHistMnth == 1)
                                {
                                    UpdateStockLYPurchMnth1(purchase.Qty, decPurValue, purchase.StockID);
                                }
                                else if (intHistMnth == 2)
                                {
                                    UpdateStockLYPurchMnth2(purchase.Qty, decPurValue, purchase.StockID);
                                }
                                else if (intHistMnth == 3)
                                {
                                    UpdateStockLYPurchMnth3(purchase.Qty, decPurValue, purchase.StockID);
                                }
                                else if (intHistMnth == 4)
                                {
                                    UpdateStockLYPurchMnth4(purchase.Qty, decPurValue, purchase.StockID);
                                }
                                else if (intHistMnth == 5)
                                {
                                    UpdateStockLYPurchMnth5(purchase.Qty, decPurValue, purchase.StockID);
                                }
                                else if (intHistMnth == 6)
                                {
                                    UpdateStockLYPurchMnth6(purchase.Qty, decPurValue, purchase.StockID);
                                }
                                else if (intHistMnth == 7)
                                {
                                    UpdateStockLYPurchMnth7(purchase.Qty, decPurValue, purchase.StockID);
                                }
                                else if (intHistMnth == 8)
                                {
                                    UpdateStockLYPurchMnth8(purchase.Qty, decPurValue, purchase.StockID);
                                }
                                else if (intHistMnth == 9)
                                {
                                    UpdateStockLYPurchMnth9(purchase.Qty, decPurValue, purchase.StockID);
                                }
                                else if (intHistMnth == 10)
                                {
                                    UpdateStockLYPurchMnth10(purchase.Qty, decPurValue, purchase.StockID);
                                }
                                else if (intHistMnth == 11)
                                {
                                    UpdateStockLYPurchMnth11(purchase.Qty, decPurValue, purchase.StockID);
                                }
                                else if (intHistMnth == 12)
                                {
                                    UpdateStockLYPurchMnth12(purchase.Qty, decPurValue, purchase.StockID);
                                }
                            }
                        }
                        else
                        {
                            if (intCurYear - intHistYear == 2)
                            {
                                if (intHistMnth > intCurMnth)
                                {
                                    if (intHistMnth == 1)
                                    {
                                        UpdateStockLYPurchMnth1(purchase.Qty, decPurValue, purchase.StockID);
                                    }
                                    else if (intHistMnth == 2)
                                    {
                                        UpdateStockLYPurchMnth2(purchase.Qty, decPurValue, purchase.StockID);
                                    }
                                    else if (intHistMnth == 3)
                                    {
                                        UpdateStockLYPurchMnth3(purchase.Qty, decPurValue, purchase.StockID);
                                    }
                                    else if (intHistMnth == 4)
                                    {
                                        UpdateStockLYPurchMnth4(purchase.Qty, decPurValue, purchase.StockID);
                                    }
                                    else if (intHistMnth == 5)
                                    {
                                        UpdateStockLYPurchMnth5(purchase.Qty, decPurValue, purchase.StockID);
                                    }
                                    else if (intHistMnth == 6)
                                    {
                                        UpdateStockLYPurchMnth6(purchase.Qty, decPurValue, purchase.StockID);
                                    }
                                    else if (intHistMnth == 7)
                                    {
                                        UpdateStockLYPurchMnth7(purchase.Qty, decPurValue, purchase.StockID);
                                    }
                                    else if (intHistMnth == 8)
                                    {
                                        UpdateStockLYPurchMnth8(purchase.Qty, decPurValue, purchase.StockID);
                                    }
                                    else if (intHistMnth == 9)
                                    {
                                        UpdateStockLYPurchMnth9(purchase.Qty, decPurValue, purchase.StockID);
                                    }
                                    else if (intHistMnth == 10)
                                    {
                                        UpdateStockLYPurchMnth10(purchase.Qty, decPurValue, purchase.StockID);
                                    }
                                    else if (intHistMnth == 11)
                                    {
                                        UpdateStockLYPurchMnth11(purchase.Qty, decPurValue, purchase.StockID);
                                    }
                                    else if (intHistMnth == 12)
                                    {
                                        UpdateStockLYPurchMnth12(purchase.Qty, decPurValue, purchase.StockID);
                                    }
                                }
                                else
                                {
                                    UpdateStockTotHistPurch(purchase.Qty / sigPackSize, decSalesValue, purchase.StockID);
                                }
                            }
                            else
                            {
                                UpdateStockTotHistPurch(purchase.Qty / sigPackSize, decSalesValue, purchase.StockID);
                            }
                        }
                    }
                }
            }

        }

        private static void UpdateStockCurrentMnthPurchDetails(double Qty, double Value, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFile SET StkPurCurrMth = @Qty, StkCostCurrMth = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);
            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }

        private static void UpdateStockTotHistPurch(double Qty, decimal Value, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistTot SET StkPurTotal = StkPurTotal + @Qty, StkCostTotal = StkCostTotal + @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);
            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }

        private static void UpdateStockCurrYearPurchMnth1(double Qty, double Value, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistCurr SET StkPurMth1 = @Qty, StkCostMth1 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);

            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }

        private static void UpdateStockCurrYearPurchMnth2(double Qty, double Value, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistCurr SET StkPurMth2 = @Qty, StkCostMth2 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);

            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }

        private static void UpdateStockCurrYearPurchMnth3(double Qty, double Value, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistCurr SET StkPurMth3 = @Qty, StkCostMth3 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);

            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }

        private static void UpdateStockCurrYearPurchMnth4(double Qty, double Value, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistCurr SET StkPurMth4 = @Qty, StkCostMth4 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);

            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }

        private static void UpdateStockCurrYearPurchMnth5(double Qty, double Value, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistCurr SET StkPurMth5 = @Qty, StkCostMth5 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);

            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }

        private static void UpdateStockCurrYearPurchMnth6(double Qty, double Value, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistCurr SET StkPurMth6 = @Qty, StkCostMth6 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);

            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }
        private static void UpdateStockCurrYearPurchMnth7(double Qty, double Value, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistCurr SET StkPurMth7 = @Qty, StkCostMth7 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);

            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }
        private static void UpdateStockCurrYearPurchMnth8(double Qty, double Value, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistCurr SET StkPurMth8 = @Qty, StkCostMth8 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);

            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }
        private static void UpdateStockCurrYearPurchMnth9(double Qty, double Value, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistCurr SET StkPurMth9 = @Qty, StkCostMth9 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);

            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }
        private static void UpdateStockCurrYearPurchMnth10(double Qty, double Value, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistCurr SET StkPurMth10 = @Qty, StkCostMth10 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);

            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }
        private static void UpdateStockCurrYearPurchMnth11(double Qty, double Value, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistCurr SET StkPurMth11 = @Qty, StkCostMth11 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);

            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }
        private static void UpdateStockCurrYearPurchMnth12(double Qty, double Value, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistCurr SET StkPurMth12 = @Qty, StkCostMth12 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);

            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }


        private static void UpdateStockLYPurchMnth1(double Qty, double Value, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistLY SET StkLYPurMth1 = @Qty, StkLYCostMth1 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);

            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }

        private static void UpdateStockLYPurchMnth2(double Qty, double Value, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistLY SET StkLYPurMth2 = @Qty, StkLYCostMth2 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);

            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }
        private static void UpdateStockLYPurchMnth3(double Qty, double Value, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistLY SET StkLYPurMth3 = @Qty, StkLYCostMth3 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);

            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }
        private static void UpdateStockLYPurchMnth4(double Qty, double Value, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistLY SET StkLYPurMth4 = @Qty, StkLYCostMth4 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);

            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }
        private static void UpdateStockLYPurchMnth5(double Qty, double Value, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistLY SET StkLYPurMth5 = @Qty, StkLYCostMth5 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);

            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }
        private static void UpdateStockLYPurchMnth6(double Qty, double Value, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistLY SET StkLYPurMth6 = @Qty, StkLYCostMth6 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);

            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }
        private static void UpdateStockLYPurchMnth7(double Qty, double Value, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistLY SET StkLYPurMth7 = @Qty, StkLYCostMth7 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);

            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }
        private static void UpdateStockLYPurchMnth8(double Qty, double Value, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistLY SET StkLYPurMth8 = @Qty, StkLYCostMth8 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);

            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }
        private static void UpdateStockLYPurchMnth9(double Qty, double Value, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistLY SET StkLYPurMth9 = @Qty, StkLYCostMth9 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);

            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }
        private static void UpdateStockLYPurchMnth10(double Qty, double Value, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistLY SET StkLYPurMth10 = @Qty, StkLYCostMth10 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);

            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }
        private static void UpdateStockLYPurchMnth11(double Qty, double Value, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistLY SET StkLYPurMth11 = @Qty, StkLYCostMth11 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);

            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }
        private static void UpdateStockLYPurchMnth12(double Qty, double Value, int StockID)
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE StockFileHistLY SET StkLYPurMth12 = @Qty, StkLYCostMth12 = @Value WHERE (StkNumber = @StockID)");
            command.Parameters.AddWithValue("@Qty", Qty);
            command.Parameters.AddWithValue("@Value", Value);

            command.Parameters.AddWithValue("@StockID", StockID);
            command.ExecuteNonQuery();
            dbConn.Close();
        }
    }
}
